﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel;

namespace AAA.ResultSet
{
    public class ExcelResultSet : AbstractResultSet
    {
        private const bool SHOW_EXCEL = false;
        private Excel.ApplicationClass _xlsApp;
        private Excel.Workbook _xlsWorkbook;
        private Excel.Worksheet _xlsSheet;
        private string _strSheetName = "Summary";

        private string _strFilename;
        private bool _hasHeader;
        private bool _checkAllValue;

        public ExcelResultSet(string strFilename, string strSheetName) : this(strFilename, strSheetName, true)
        {            
        }

        public ExcelResultSet(string strFilename, string strSheetName, bool hasHeader)
            : this(strFilename, strSheetName, hasHeader, false)
        {
        }
        public ExcelResultSet(string strFilename, string strSheetName, bool hasHeader, bool checkAllValue) : base()
        {
            _strFilename = strFilename;
            _hasHeader = hasHeader;
            _checkAllValue = checkAllValue;
            SheetName = strSheetName;
        }


        public override bool Load()
        {
            int iColumnIndex;
            int iRowIndex;
            Excel.Range xlsRange = null;
            string strValue;
            object[] oValues;
            bool hasValue;
            try
            {
                ExcelOpen(_strFilename);

                if (_xlsSheet == null)
                {
                    ErrorMessage = "所指定的WorkSheet不存在";
                    IsSuccess = false;
                    return IsSuccess;
                }

                iColumnIndex = 1;
                while (true)
                {
                    xlsRange = (Range)_xlsSheet.Cells[1, iColumnIndex];
                    strValue = xlsRange.Value == null ? "" : (string)xlsRange.Value.ToString();
                    if (strValue == "")
                        break;

                    if (_hasHeader)
                        AddColumn(strValue.Trim());
                    else
                        AddColumn("Column" + iColumnIndex);

                    iColumnIndex++;
                }
                
                iRowIndex = _hasHeader ? 2 : 1;
                while (true)
                {
                    if (IsDoEvents)
                        System.Windows.Forms.Application.DoEvents();

                    oValues = new object[ColumnCount];

                    xlsRange = (Range)_xlsSheet.Cells[iRowIndex, 1];
                    strValue = (string)xlsRange.Text;

                    if ((strValue.Trim() == "") && (_checkAllValue == false))
                        break;

                    oValues[0] = strValue;
                    
                    for (int i = 1; i < ColumnCount; i++)
                    {
                        xlsRange = (Range)_xlsSheet.Cells[iRowIndex, i + 1];
                        strValue = xlsRange.Value == null ? "" : (string)xlsRange.Value.ToString();
                        if (xlsRange.Validation != null)
                            hasValue = true;
                        oValues[i] = strValue;
                    }

                    hasValue = false;
                    for(int i = 0; i < oValues.Length; i++)
                        if ((hasValue = (oValues[i].ToString().Trim() != "")))
                            break;
                    if (hasValue == false)
                        break;

                    AddRow(oValues);
                    iRowIndex++;
                }

                IsSuccess = true;
            }
            catch (Exception ex)
            {
                IsSuccess = false;
                ErrorMessage = ex.Message + "," + ex.StackTrace;
            }
            finally
            {
                IsSuccess = ExcelClose();                   
            }
            return IsSuccess;
        }

        public string SheetName
        {
            get { return _strSheetName; }
            set { _strSheetName = value; }
        }

        private bool ExcelClose()
        {
            try
            {                
                _xlsWorkbook.Close(false, null, null);
                _xlsApp.Workbooks.Close();
                _xlsApp.Quit();                
                IsSuccess = true;
            }
            catch(Exception ex)
            {
                IsSuccess = false;
                ErrorMessage = ex.Message + "," + ex.StackTrace;
            }
            return IsSuccess;

        }

        private bool ExcelOpen(string strFile)
        {
            try
            {
                if (_xlsApp == null)
                    _xlsApp = new Excel.ApplicationClass();
                _xlsWorkbook = _xlsApp.Workbooks.Open(strFile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true);
                _xlsApp.Visible = SHOW_EXCEL;
                _xlsSheet = (Excel.Worksheet)_xlsApp.Sheets[SheetName];
                _xlsApp.Calculation = XlCalculation.xlCalculationManual;
                IsSuccess = true;
            }
            catch (Exception ex)
            {
                IsSuccess = false;
                ErrorMessage = ex.Message + "," + ex.StackTrace;
            }
            return IsSuccess;
        }

    }
}
